﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SQLite;

namespace DAL
{
    /// <summary>
    /// 通用数据访问类
    /// </summary>
    class SQLHelper
    {
        private static String connString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

        /// <summary>
        /// 执行 增（insert）、删（delete）、改（update） 方法
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>返回影响行数</returns>
        public static int Update(String sql)
        {
            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(sql,conn);

            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw new Exception("执行public static int Update(String sql)发生异常：" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }


        /// <summary>
        /// 执行单一结果查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>返回单一查询结果 object类型</returns>
        public static object GetSingleResult(String sql)
        {
            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(sql ,conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {

                throw new Exception("执行public static object GetSingleResult(String sql)发生异常：" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }


        /// <summary>
        /// 执行一个结果集的查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>返回SQLiteDataReader对象</returns>
        public static SQLiteDataReader GetReader(String sql)
        {
            SQLiteConnection conn = new SQLiteConnection(connString);
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);

            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                conn.Close();
                throw new Exception ("执行public static SQLiteDataReader GetReader(String sql)发生异常："+ex.Message);
            }

        }


        /// <summary>
        /// 获取数据集对象（DataSet）
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <param name="DataSetTableName">数据集表名</param>
        /// <returns>返回数据集对象（DataSet）</returns>
        public static DataSet GetDataSet(String sqlStr, String DataSetTableName)
        {
            SQLiteConnection conn = new SQLiteConnection(connString);   //创建连接对象
            SQLiteDataAdapter sQLiteDataAdapter = new SQLiteDataAdapter(sqlStr, conn);  //创建数据库匹配对象
            DataSet myds = new DataSet();   //创建数据集对象
            sQLiteDataAdapter.Fill(myds, DataSetTableName);    //填充数据集
            return myds;   //返回填充后的数据集对象
        }


    }
}
